1 <?php
2 $currDir = dirname(__FILE__);
3 require("{$currDir}/incCommon.php");
4 $GLOBALS['page_title'] = $Translation['view or rebuild fields'];
5 include("{$currDir}/incHeader.php");
6
7 /* application schema as created in AppGini */
8 $schema = array(
9 'patients' => array(
10 'id' => array('appgini' => 'INT unsigned not null primary key auto_increment '),
11 'last_name' => array('appgini' => 'VARCHAR(40) not null '),
12 'first_name' => array('appgini' => 'VARCHAR(40) not null '),
13 'gender' => array('appgini' => 'VARCHAR(10) not null default \'Unknown\' '),
14 'sexual_orientation' => array('appgini' => 'TEXT not null '),
15 'birth_date' => array('appgini' => 'DATE '),
16 'age' => array('appgini' => 'INT '),
17 'image' => array('appgini' => 'VARCHAR(40) '),
18 'address' => array('appgini' => 'TEXT '),
19 'city' => array('appgini' => 'VARCHAR(40) '),
20 'state' => array('appgini' => 'VARCHAR(15) '),
21 'zip' => array('appgini' => 'CHAR(8) '),
22 'home_phone' => array('appgini' => 'VARCHAR(40) '),
23 'work_phone' => array('appgini' => 'VARCHAR(40) '),
24 'mobile' => array('appgini' => 'VARCHAR(40) '),
25 'tobacco_usage' => array('appgini' => 'VARCHAR(40) not null default \'Unknown\' '),
26 'alcohol_intake' => array('appgini' => 'VARCHAR(40) not null default \'Unknown\' '),
27 'history' => array('appgini' => 'VARCHAR(100) not null default \'Unknown\' '),
28 'surgical_history' => array('appgini' => 'TEXT '),
29 'obstetric_history' => array('appgini' => 'TEXT '),
30 'genetic_diseases' => array('appgini' => 'TEXT '),
31 'contact_person' => array('appgini' => 'VARCHAR(100) '),
32 'other_details' => array('appgini' => 'TEXT '),
33 'comments' => array('appgini' => 'TEXT '),
34 'filed' => array('appgini' => 'DATETIME '),
35 'last_modified' => array('appgini' => 'DATETIME ')
36 ),
37 'disease_symptoms' => array(
38 'id' => array('appgini' => 'INT unsigned not null primary key auto_increment '),
39 'disease' => array('appgini' => 'VARCHAR(200) '),
40 'symptoms' => array('appgini' => 'TEXT '),
41 'reference' => array('appgini' => 'TEXT ')
42 ),
43 'medical_records' => array(
44 'id' => array('appgini' => 'INT unsigned not null primary key auto_increment '),
45 'patient' => array('appgini' => 'INT unsigned '),
46 'image_1' => array('appgini' => 'VARCHAR(40) '),
47 'image_2' => array('appgini' => 'VARCHAR(40) '),
48 'image_3' => array('appgini' => 'VARCHAR(40) '),
49 'image_4' => array('appgini' => 'VARCHAR(40) '),
50 'image_5' => array('appgini' => 'VARCHAR(40) '),
51 'document_1' => array('appgini' => 'VARCHAR(40) '),
52 'document_2' => array('appgini' => 'VARCHAR(40) '),
53 'document_3' => array('appgini' => 'VARCHAR(40) '),
54 'document_4' => array('appgini' => 'VARCHAR(40) '),
55 'document_5' => array('appgini' => 'VARCHAR(40) '),
56 'description' => array('appgini' => 'TEXT ')
57 ),
58 'events' => array(
59 'id' => array('appgini' => 'INT unsigned not null primary key auto_increment '),
60 'title' => array('appgini' => 'VARCHAR(40) '),
61 'date' => array('appgini' => 'DATE '),
62 'status' => array('appgini' => 'VARCHAR(40) not null '),
63 'name_patient' => array('appgini' => 'INT unsigned '),
64 'time' => array('appgini' => 'TIME default \'12:00\' '),
65 'prescription' => array('appgini' => 'VARCHAR(40) '),
66 'diagnosis' => array('appgini' => 'VARCHAR(40) '),
67 'comments' => array('appgini' => 'TEXT ')
68 )
69 );
70
71 $table_captions = getTableList();
72
73 /* function for preparing field definition for comparison */
74 function prepare_def($def){
75 $def = trim($def);
76 $def = strtolower($def);
77
78 /* ignore length for int data types */
79 $def = preg_replace('/int\w*\([0-9]+\)/', 'int', $def);
80
81 /* make sure there is always a space before mysql words */
82 $def = preg_replace('/(\S)(unsigned|not null|binary|zerofill|auto_increment|default)/', '$1 $2', $def);
83
84 /* treat 0.000.. same as 0 */
85 $def = preg_replace('/([0-9])*\.0+/', '$1', $def);
86
87 /* treat unsigned zerofill same as zerofill */
88 $def = str_ireplace('unsigned zerofill', 'zerofill', $def);
89
90 /* ignore zero-padding for date data types */
91 $def = preg_replace("/date\s*default\s*'([0-9]{4})-0?([1-9])-0?([1-9])'/i", "date default '$1-$2-$3'", $def);
92
93 return $def;
94 }
95
96 /* process requested fixes */
97 $fix_table = (isset($_GET['t']) ? $_GET['t'] : false);
98 $fix_field = (isset($_GET['f']) ? $_GET['f'] : false);
99
100 if($fix_table && $fix_field && isset($schema[$fix_table][$fix_field])){
101 $field_added = $field_updated = false;
102
103 // field exists?
104 $res = sql("show columns from `{$fix_table}` like '{$fix_field}'", $eo);
105 if($row = db_fetch_assoc($res)){
106 // modify field
107 $qry = "alter table `{$fix_table}` modify `{$fix_field}` {$schema[$fix_table][$fix_field]['appgini']}";
108 sql($qry, $eo);
109 $field_updated = true;
110 }else{
111 // create field
112 $qry = "alter table `{$fix_table}` add column `{$fix_field}` {$schema[$fix_table][$fix_field]['appgini']}";
113 sql($qry, $eo);
114 $field_added = true;
115 }
116 }
117
118 foreach($table_captions as $tn => $tc){
119 $eo['silentErrors'] = true;
120 $res = sql("show columns from `{$tn}`", $eo);
121 if($res){
122 while($row = db_fetch_assoc($res)){
123 if(!isset($schema[$tn][$row['Field']]['appgini'])) continue;
124 $field_description = strtoupper(str_replace(' ', '', $row['Type']));
125 $field_description = str_ireplace('unsigned', ' unsigned', $field_description);
126 $field_description = str_ireplace('zerofill', ' zerofill', $field_description);
127 $field_description = str_ireplace('binary', ' binary', $field_description);
128 $field_description .= ($row['Null'] == 'NO' ? ' not null' : '');
129 $field_description .= ($row['Key'] == 'PRI' ? ' primary key' : '');
130 $field_description .= ($row['Key'] == 'UNI' ? ' unique' : '');
131 $field_description .= ($row['Default'] != '' ? " default '" . makeSafe($row['Default']) . "'" : '');
132 $field_description .= ($row['Extra'] == 'auto_increment' ? ' auto_increment' : '');
133
134 $schema[$tn][$row['Field']]['db'] = '';
135 if(isset($schema[$tn][$row['Field']])){
136 $schema[$tn][$row['Field']]['db'] = $field_description;
137 }
138 }
139 }
140 }
141 ?>
142
143 <?php if($field_added || $field_updated){ ?>
144 <div class="alert alert-info alert-dismissable">
145 <button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>
146 <i class="glyphicon glyphicon-info-sign"></i>
147 <?php
148 $originalValues = array ('<ACTION>','<FIELD>' , '<TABLE>' , '<QUERY>' );
149 $action = ($field_added ? 'create' : 'update');
150 $replaceValues = array ( $action , $fix_field , $fix_table , $qry );
151 echo str_replace ( $originalValues , $replaceValues , $Translation['create or update table'] );
152 ?>
153 </div>
154 <?php } ?>
155
156 <div class="page-header"><h1>
157 <?php echo $Translation['view or rebuild fields'] ; ?>
158 <button type="button" class="btn btn-default" id="show_deviations_only"><i class="glyphicon glyphicon-eye-close"></i> <?php echo $Translation['show deviations only'] ; ?></button>
159 <button type="button" class="btn btn-default hidden" id="show_all_fields"><i class="glyphicon glyphicon-eye-open"></i> <?php echo $Translation['show all fields'] ; ?></button>
160 </h1></div>
161
162 <p class="lead"><?php echo $Translation['compare tables page'] ; ?></p>
163
164 <div class="alert summary"></div>
165 <table class="table table-responsive table-hover table-striped">
166 <thead><tr>
167 <th></th>
168 <th><?php echo $Translation['field'] ; ?></th>
169 <th><?php echo $Translation['AppGini definition'] ; ?></th>
170 <th><?php echo $Translation['database definition'] ; ?></th>
171 <th></th>
172 </tr></thead>
173
174 <tbody>
175 <?php foreach($schema as $tn => $fields){ ?>
176 <tr class="text-info"><td colspan="5"><h4 data-placement="left" data-toggle="tooltip" title="<?php echo str_replace ( "<TABLENAME>" , $tn , $Translation['table name title']) ; ?>"><i class="glyphicon glyphicon-th-list"></i> <?php echo $table_captions[$tn]; ?></h4></td></tr>
177 <?php foreach($fields as $fn => $fd){ ?>
178 <?php $diff = ((prepare_def($fd['appgini']) == prepare_def($fd['db'])) ? false : true); ?>
179 <?php $no_db = ($fd['db'] ? false : true); ?>
180 <tr class="<?php echo ($diff ? 'warning' : 'field_ok'); ?>">
181 <td><i class="glyphicon glyphicon-<?php echo ($diff ? 'remove text-danger' : 'ok text-success'); ?>"></i></td>
182 <td><?php echo $fn; ?></td>
183 <td class="<?php echo ($diff ? 'bold text-success' : ''); ?>"><?php echo $fd['appgini']; ?></td>
184 <td class="<?php echo ($diff ? 'bold text-danger' : ''); ?>"><?php echo thisOr($fd['db'], $Translation['does not exist']); ?></td>
185 <td>
186 <?php if($diff && $no_db){ ?>
187 <a href="pageRebuildFields.php?t=<?php echo $tn; ?>&f=<?php echo $fn; ?>" class="btn btn-success btn-xs btn_create" data-toggle="tooltip" data-placement="top" title="<?php echo $Translation['create field'] ; ?>"><i class="glyphicon glyphicon-plus"></i> <?php echo $Translation['create it'] ; ?></a>
188 <?php }elseif($diff){ ?>
189 <a href="pageRebuildFields.php?t=<?php echo $tn; ?>&f=<?php echo $fn; ?>" class="btn btn-warning btn-xs btn_update" data-toggle="tooltip" title="<?php echo $Translation['fix field'] ; ?>"><i class="glyphicon glyphicon-cog"></i> <?php echo $Translation['fix it'] ; ?></a>
190 <?php } ?>
191 </td>
192 </tr>
193 <?php } ?>
194 <?php } ?>
195 </tbody>
196 </table>
197 <div class="alert summary"></div>
198
199 <style>
200 .bold{ font-weight: bold; }
201 [data-toggle="tooltip"]{ display: block !important; }
202 </style>
203
204 <script>
205 jQuery(function(){
206 jQuery('[data-toggle="tooltip"]').tooltip();
207
208 jQuery('#show_deviations_only').click(function(){
209 jQuery(this).addClass('hidden');
210 jQuery('#show_all_fields').removeClass('hidden');
211 jQuery('.field_ok').hide();
212 });
213
214 jQuery('#show_all_fields').click(function(){
215 jQuery(this).addClass('hidden');
216 jQuery('#show_deviations_only').removeClass('hidden');
217 jQuery('.field_ok').show();
218 });
219
220 jQuery('.btn_update').click(function(){
221 return confirm("<?php echo $Translation['field update warning'] ; ?>");
222 });
223
224 var count_updates = jQuery('.btn_update').length;
225 var count_creates = jQuery('.btn_create').length;
226 if(!count_creates && !count_updates){
227 jQuery('.summary').addClass('alert-success').html("<?php echo $Translation['no deviations found'] ; ?>");
228 }else{
229 var fieldsCount = "<?php echo $Translation['error fields']; ?>";
230 fieldsCount = fieldsCount.replace(/<CREATENUM>/, count_creates ).replace(/<UPDATENUM>/, count_updates);
231
232
233 jQuery('.summary')
234 .addClass('alert-warning')
235 .html(
236 fieldsCount
237 );
238 }
239 });
240 </script>
241
242 <?php
243 include("{$currDir}/incFooter.php");
244 ?>
